<?php
/**
 * Created by PhpStorm.
 * User: v.xiaqc
 * Date: 2019/2/15
 * Time: 15:49
 */

namespace app\controllers;


use app\models\Country;
use app\models\CountryModel;
use yii\web\Controller;
use Yii;
use yii\db\Connection;
class SqlQueryController extends Controller
{
    public function actionSelect()
    {
        // 返回多行. 每行都是列名和值的关联数组.
        $country = Yii::$app->db->createCommand('select * from {{%country}}')
            ->queryAll();

        //返回一行
        $country = Yii::$app->db->createCommand('select * from country')
            ->queryOne();

        //返回一列 （第一列）
        $country = Yii::$app->db->createCommand('select name from country')
            ->queryColumn();

        //返回一个标量值
        $count = Yii::$app->db->createCommand('select count(*) from country')
            ->queryScalar();


        //绑定参数
        $country = Yii::$app->db->createCommand('select * from country where code=:code')
            ->bindValue(':code', 'AU')
            ->queryOne();

        //绑定多个参数
        $param = [
            ':code' => 'AU',
            ':name' => 'Australia'
        ];
        $country = Yii::$app->db->createCommand('select * from country where code=:code and name=:name')
            ->bindValues($param)
            ->queryOne();

        //使用不同的参数多次执行
        $command = Yii::$app->db->createCommand('select * from country where code=:code');

        $country1 = $command->bindValue(':code', 'AU')->queryOne();
        $country2 = $command->bindValue(':code', 'BR')->queryOne();

        //通过引用来绑定参数
        $command = Yii::$app->db->createCommand('select * from country where code=:code')
            ->bindParam(':code', $code);

        $code = 'AU';
        $country1 = $command->queryOne();
        $code = 'BR';
        $country2 = $command->queryOne();


        //执行非查询语句
        Yii::$app->db->createCommand('UPDATE country set code="AA" where code="DD"')
            ->execute();

        //INSERT
        $str = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
        $code = substr(str_shuffle($str), rand(0, 25), 1) . substr(str_shuffle($str), rand(-26, 25), 1);
        Yii::$app->db->createCommand()->insert('country', [
            'code' => $code,
            'name' => 'SSDS',
            'population' => 123
        ])->execute();

        //UPDATE
        Yii::$app->db->createCommand()->update('country', ['code' => 'HH'], 'name="SSDS1" and population=123')
            ->execute();

        //DELETE
        Yii::$app->db->createCommand()->delete('country', 'name="SSDS" and population=123')
            ->execute();

        //插入多行
//        Yii::$app->db->createCommand()->batchInsert('country', ['code', 'name', 'population'], [
//            ['GG', 'JJJ', 43],
//            ['KK', 'sfs', 55]
//        ])->execute();

        //如果它们不存在（匹配唯一约束），则将行插入到数据库表中， 或者在它们执行时更新它们
        $params = [];
        Yii::$app->db->createCommand()->upsert('pages', [
            'name' => 'Front page',
            'id' => 'http://example.com/', // url is unique
            'visits' => 0,
        ], [
            'visits' => new \yii\db\Expression('visits + 1'),
        ], $params)->execute();

        //引用表和列名称
        $pages = Yii::$app->db->createCommand('select count([[id]]) from {{pages}}')
            ->getSql();
        //echo $pages; //select count(`id`) from `pages`

        //使用表前缀
        $pages = Yii::$app->db->createCommand('select count([[id]]) from {{%pages}}')
            ->getSql();
        //echo $pages; //select count(`id`) from `tpl_pages`

        //执行事务
        Yii::$app->db->transaction(function($db) {
            //$db->createCommand('UPDATE country set code="AU" where name="123"')->execute();
            //$db->createCommand('UPDATE country set code="GG" where name="JJJ"')->execute();
            // ... executing other SQL statements ...
        });
        echo '<pre>';
        var_dump((new CountryModel())->get());
    }
}